﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using ReflectionStudio.Core.Events;
using INTEX.DataBase;
using INTEX.DataBase.Tools;
using System.Text;
namespace WPFExcelReport
{

    public class YCAFSACCData : BaseFunction 
    {
        public bool checkVK07 = true;
        SETINTEXDB INTEXDB = new SETINTEXDB();
        INTEXDBC INTEXDBC;
        public YCAFSACCData()
            : base()
        {

        }
        public YCAFSACCData(OracleConnection _myConnection, IntexReptServBLClient _proxy, string userID = "        ")
            : base(_myConnection, _proxy, userID)
        {
            INTEXDBC = new INTEXDBC(OracleDB, proxy);
        }
        public YCAFSACCData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {

        }
        public DataSet GetFSACC(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "ICC_FSACC";
            string ARTICLE = dwABPOS["ARTICLE"].ToString().Trim();
            string TYPE = dwABPOS["TYPE"].ToString().Trim();
            string PRDGRP = dwABPOS["PRDGRP"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = " SELECT DISTINCT FSART,FSABEZ,FSPROGRP,DES,FSARTGRP,PRDGRP, "
                    + " STRAGG(DISTINCT TRIM(FSFFARBE))OVER(PARTITION BY FSART, LPRICE, LIEFNR ) AS COLOR, "
                    + " STRAGG(DISTINCT TRIM(FSGGROE)) OVER(PARTITION BY FSART, LPRICE, LIEFNR) AS ISIZE, "
                    + " LPRICE,LIEFNR,LSNAME FROM ( "
                    + " SELECT A.FSART,A.FSABEZ,A.FSPROGRP,A.DES,A.FSARTGRP,A.PRDGRP,A.FSFFARBE,A.COLOR,A.FSGGROE, "
                    + " NVL(NVL(NVL(TRIM(H.FELIEFNR),TRIM(G.FELIEFNR)),TRIM(F.FELIEFNR)),TRIM(E.FELIEFNR)) AS LIEFNR, "
                    + " NVL(NVL(NVL(TRIM(H.LSNAME1),TRIM(G.LSNAME1)),TRIM(F.LSNAME1)),TRIM(E.LSNAME1)) AS LSNAME, "
                    + " NVL(NVL(NVL(NVL(H.LPRICE,G.LPRICE),F.LPRICE),E.LPRICE),0) AS LPRICE "
                    + " FROM (SELECT A.FSART,A.FSABEZ,A.FSPROGRP,B.DES,A.FSARTGRP, "
                    + " (select trim(substr(sddaten, 2, 20)) from sddat where sdsa = '0178' "
                    + " and substr(sdkey, 24, 1) = 'V' and trim(substr(sdkey, 3, 15)) = trim(a.fsartgrp)) AS PRDGRP, "
                    + " C.FSFFARBE,TRIM(C.FSFFBEZ) AS COLOR,D.FSGGROE "
                    + " FROM FSSTAMM A, (select SUBSTR(SDKEY, 3, 3) AS CODE, SUBSTR(SDDATEN, 2, 30) AS DES  from sddat "
                    + " where sdsa = '0196'   and substr(sdkey, 24, 1) = 'V' AND SUBSTR(SDDATEN, 32, 3) = 'ACC') B, "
                    + " FSFARBEN C,FSGROE D "
                    + " WHERE A.FSPROGRP = B.CODE AND A.FSMANDANT = D.FSGMANDANT(+) AND A.FSART = D.FSGART(+) "
                    + " AND A.FSVAR = D.FSGVAR(+) AND A.FSMANDANT = C.FSFMANDANT(+) "
                    + " AND A.FSART = C.FSFART(+) AND A.FSVAR = C.FSFVAR(+) "
                    + " AND A.FSSTAKZ = 'A' and a.fsart like '" + ARTICLE + "%'  "
                    + " AND (TRIM(A.fsartgrp) = '" + PRDGRP + "' OR '" + PRDGRP + "'  = 'XXXXXX') "
                    + " AND (TRIM(A.FSPROGRP) = '" + TYPE + "' OR '" + TYPE + "'  = 'XXXXXX')) A, "
                    + " (select e.feart,e.fefarbe,e.fegroe,e.feliefnr,e1.lsname1, "
                    + " decode(e.feminbslm1,0,e.fepreis1*0.01,e.fepreis1*0.01/e.feminbslm1) as lprice  "
                    + " from fseink e,liefdat e1 "
                    + " where e.femandant = e1.lsmandant and e.feliefnr = e1.lsliefnr  "
                    + " and e.fefarbe = '          ' and e.fegroe = '       ') E , "
                    + " (select e.feart,e.fefarbe,e.fegroe,e.feliefnr,e1.lsname1, "
                    + " decode(e.feminbslm1,0,e.fepreis1*0.01,e.fepreis1*0.01/e.feminbslm1) as lprice  "
                    + " from fseink e,liefdat e1 "
                    + " where e.femandant = e1.lsmandant and e.feliefnr = e1.lsliefnr  "
                    + " and trim(e.fefarbe) is not null and e.fegroe = '       ') F, "
                    + " (select e.feart,e.fefarbe,e.fegroe,e.feliefnr,e1.lsname1 , "
                    + " decode(e.feminbslm1,0,e.fepreis1*0.01,e.fepreis1*0.01/e.feminbslm1) as lprice "
                    + " from fseink e,liefdat e1 "
                    + " where e.femandant = e1.lsmandant and e.feliefnr = e1.lsliefnr  "
                    + " and e.fefarbe = '          ' and trim(e.fegroe)is not null) G, "
                    + " (select e.feart,e.fefarbe,e.fegroe,e.feliefnr,e1.lsname1, "
                    + " decode(e.feminbslm1,0,e.fepreis1*0.01,e.fepreis1*0.01/e.feminbslm1) as lprice  "
                    + " from fseink e,liefdat e1 "
                    + " where e.femandant = e1.lsmandant and e.feliefnr = e1.lsliefnr  "
                    + " and trim(e.fefarbe)is not null and trim(e.fegroe)is not null ) H "
                    + " WHERE A.FSART = E.FEART(+)  "
                    + " AND A.FSART = F.FEART(+) AND A.FSFFARBE = F.FEFARBE(+)  "
                    + " AND A.FSART = G.FEART(+) AND A.FSGGROE = G.FEGROE(+) "
                    + " AND A.FSART = H.FEART(+) AND A.FSFFARBE = H.FEFARBE(+) AND A.FSGGROE = H.FEGROE(+) "
                    + " )order by 1 "
                                     
                   ;

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["FSABEZ"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["FSABEZ"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["DES"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["DES"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["PRDGRP"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["PRDGRP"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["LSNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["LSNAME"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet GetData(DataRow dwInput)
        {
            DataSet SReturn = new DataSet();
            DataSet SResult = new DataSet();

            try
            {
                DataRow dwTemp;
                SResult = GetFSACC(dwInput);
                SReturn.Merge(SResult); 
            }
            catch (Exception ex)
            {
                Tracer.Error(ABPOS + ex.StackTrace, ex);
            }
            finally
            {
                EventDispatcher.Instance.RaiseStatus("查询完成", StatusEventType.StopProgress);
            }
            return SReturn;
        } 
    }
}